# -*- coding: utf-8 -*-
import xlrd
import time
import datetime
from operator import itemgetter, attrgetter

def read_excel():

    #文件位置

    ExcelFile=xlrd.open_workbook(r'/Users/ydcun-pro/Desktop/25235653_2_2018暑期科研营(人工智能，高中)_30_30.xls')

    #获取目标EXCEL文件sheet名

    print(ExcelFile.sheet_names())

    #------------------------------------

    #若有多个sheet，则需要指定读取目标sheet例如读取sheet2

    #sheet2_name=ExcelFile.sheet_names()[1]

    #------------------------------------

    #获取sheet内容【1.根据sheet索引2.根据sheet名称】

    #sheet=ExcelFile.sheet_by_index(1)

    sheet=ExcelFile.sheet_by_name('Sheet1')

    #打印sheet的名称，行数，列数

    print(sheet.name,str(sheet.nrows) + "行", str(sheet.ncols) + "列")

    #获取整行或者整列的值
    rows=sheet.row_values(2)#第三行内容
    cols=sheet.col_values(1)#第二列内容
    #获取单元格内容
    # print(sheet.cell(1,0).value.encode('utf-8'))
    # print(sheet.cell_value(1,0).encode('utf-8'))
    # 姓名
    # print(sheet.row(1)[6].value)
    allNameList = sheet.col_values(13)
    nameList = [ v for v in sheet.col_values(6) if v!=""]
    inHotelList = []
    outHotelList = []
    noHotelList = [v for v in allNameList if v not in nameList]
    for i in range(1,len(nameList)):
        row = {}
        row["name"] = sheet.cell(i,6).value
        row["sex"] = sheet.cell(i,7).value
        row["phone"] = sheet.cell(i,8).value
        row["isHotel"] = sheet.cell(i,9).value
        row["inDate"] = 0
        row["remark"] = sheet.cell(i,12)
        if(row["isHotel"]==1.0):
            row["inDate"] = datetime.datetime.strptime(sheet.cell(i,10).value, "%Y-%m-%d")
            row["outDate"] = datetime.datetime.strptime(sheet.cell(i,11).value, "%Y-%m-%d")
            inHotelList.append(row)
            continue
        outHotelList.append(row)
    inHotelList = sorted(inHotelList, key=lambda stu:(stu['sex'], stu['inDate'],stu["outDate"]))
    print(inHotelList)
    print(outHotelList)

    print("住宿报表:")
    tempSex = 0
    count = 0
    index = 0
    for row in inHotelList:
        if(count%2==0):
            print("")
        if(count>1):
            if(inHotelList[index-1]["sex"] != inHotelList[index]["sex"]):
                print("")
                count=0

        print(" ",row["name"],row["phone"],datetime.datetime.strftime(row["inDate"], "%Y-%m-%d"),datetime.datetime.strftime(row["outDate"], "%Y-%m-%d"),end="")
        count = count+1
        index = index+1


    print("\n确定不住宿名单:")
    for n in outHotelList:
        print(n["name"])
    print("\n未填写住宿信息名单:")
    for n in noHotelList:
        print(n)

if __name__ =='__main__':
    read_excel()